﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class newwordList : CWebBase
{
    public class SearchValue
    {
        public string searchLevel1 = "";
        public string searchLevel2 = "";
        public string searchLevel3 = "";

        public string wordCount1 = "";
        public string wordCount2 = "";
        public string wordCount3 = "";
        public string wordCount4 = "";

        public string searchWord = "";
    }

    public int _page, _listcount;
    public int _totalcount, _pagecount = 10;
    public string searchJson = "";
    public string _pagefield = "";

    public CDatabase _db = new CDatabase();
    public SearchValue sv = new SearchValue();

    public ArrayList resultList = new ArrayList();
    public string _pagename = "/analyze_new/admin/newword/newwordList.aspx";
    public Boolean isExcelType = false;
    private string where;

    protected void Page_Load(object sender, EventArgs e)
    {
        _db.GetConnection();

        isExcelType = (CheckNull(Request["excelType"]) == "xls")? true : false;

        _page = Convert.ToInt32(CheckNull(Request["page"], "1"));
        _listcount = Convert.ToInt32(CheckNull(Request["listcount"], "10"));

        // 검색
        sv.searchLevel1 = CheckNull(Request["searchLevel1"], "0");
        sv.searchLevel2 = CheckNull(Request["searchLevel2"], "");
        sv.searchLevel3 = CheckNull(Request["searchLevel3"], "");

        sv.wordCount1 = CheckNull(Request["wordCount1"], "");
        sv.wordCount2 = CheckNull(Request["wordCount2"], "");
        sv.wordCount3 = CheckNull(Request["wordCount3"], "");
        sv.wordCount4 = CheckNull(Request["wordCount4"], "");

        sv.searchWord = CheckNull(Request["searchWord"], "");

        // 검색 기본
        makeBaseWhere();

        // sql
        string countSql = "Select count(*) From ";
        string listSql = "Select "+
                         "  wd.Word, wd.WordClass, wd.WordMean, wd.HeadWord, wd.WordLevel, lv.LevelId, lv.LevelName, wd.UnitNum, WordCnt, "+
                         "  ROW_NUMBER() OVER(ORDER BY wd.WordLevel ASC, wd.Word ASC) ROWNUM "+
                         
                         "From ";

        string wordSql = "(Select "+
                         "LevelId, Word, WordClass, WordMean, HeadWord, WordLevel, UnitNum "+
                         ", (Select Count(*) From ANALYZE_NEW_WORD Z, ANALYZE_WORD_LEVEL T Where z.LevelId = T.LevelId and Z.Word = A.Word and Z.WordClass = A.WordClass and Z.WordMean = A.WordMean ) as WordCnt " +
                         "From ANALYZE_NEW_WORD A) wd";

        string LevelSql = "(Select " +
                          " (Select TopLevelId From ANALYZE_WORD_LEVEL where LevelId = A.TopLevelId) TTopLevelId," +
                          " TopLevelId, LevelId, LevelName, LevelSeq" +
                          " from ANALYZE_WORD_LEVEL A where len(LevelId) = 9 and DelYn <> 'Y' ) lv";

        countSql = countSql + wordSql + ", " + LevelSql + where;

        _totalcount = _db.GetQueryInt(countSql);


        int start = (Convert.ToInt32(_page) - 1) * Convert.ToInt32(_listcount) + 1;
        int end = Convert.ToInt32(_page) * Convert.ToInt32(_listcount);

        // Excel Down Load
        if (isExcelType)
        {
            start = 1;
            end = _totalcount;
        }

        listSql = "Select * From ("+listSql + wordSql + ", " + LevelSql + where +") T "+
                  " WHERE ROWNUM BETWEEN " + start + " AND " + end;

        SqlDataReader dr = _db.GetQueryResult(listSql);

        ResultList.DataSource = dr;
        ResultList.DataBind();

        dr.Close();

        if (isExcelType) {
            string statistics = CheckNull(Request["excel_contents"]);
            string filename = DateTime.Now.ToString("yyyy-MM-dd_HHmmss") + ".xls";

            Response.ContentType = "application/vnd.ms-excel";
            Response.ContentType = "application/x-msexcel";
            Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
            Response.Write("<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />");
            Response.Write(statistics.Replace("<table", "<table border=1"));
        }
    }

    private void makeBaseWhere() {
        where = " where lv.LevelId = wd.LevelId ";

        // Level 1
        if (!isEmpty(sv.searchLevel1) && sv.searchLevel1 != "0")
        {
            where += " and lv.TTopLevelId = '" + sv.searchLevel1 + "'";
        }
        // Level 2
        if (!isEmpty(sv.searchLevel2))
        {
            where += " and lv.TopLevelId = '" + sv.searchLevel2 + "'";
        }
        // Level 3
        if (!isEmpty(sv.searchLevel3))
        {
            where += " and lv.LevelId = '" + sv.searchLevel3 + "'";
        }

        // search word
        if (!isEmpty(sv.searchWord)) {
            where += " and (wd.Word Like '%" + sv.searchWord + "%' or wd.HeadWord Like '%" + sv.searchWord + "%')";
        }

        if (!isEmpty(sv.wordCount1) || !isEmpty(sv.wordCount2) || !isEmpty(sv.wordCount3) || !isEmpty(sv.wordCount4) ) {
            where += " and ( ## ";

            if (!isEmpty(sv.wordCount1))
            {
                where += " or WordCnt = 1"; 
            }

            if (!isEmpty(sv.wordCount2))
            {
                where += " or WordCnt = 2";
            }

            if (!isEmpty(sv.wordCount3))
            {
                where += " or WordCnt = 3";
            }

            if (!isEmpty(sv.wordCount4))
            {
                where += " or WordCnt > 3";
            }

            where = where.Replace("##  or", "") + ")";
        }
    }

    public string getLevel(object level)
    {
        string lv = level as string;
        string result = "";

        if (lv == "8")
        {
            result = "기타";
        }
        else {
            result = "Level " + lv;
        }

        return result;
    }

    protected void Page_Unload(object sender, EventArgs e)
    {
        _db.ReleaseConnection();
    }
}
